/*
 * Copyright (C) <2009>  <Matthew Dickinson>
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

package com.pulsarwebdevelopment.concordance;

import java.io.File;
import java.io.StringWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.regex.*;

/** 
 * Maps words to the verses in which they appear
 * @author Matthew Dickinson
 * Last updated: 8/22/2009
 */
public class Concordance{
	
	public static class ConcordanceInfo{
		private static final Pattern UNIQUE_STRING_FORMAT = Pattern.compile("(\\d+) - (.*)");
		
		public int id;
		public String desc;
		
		public ConcordanceInfo(int id, String desc) {
			this.id = id;
			this.desc = desc;
		}
		
		@Override
		public boolean equals(Object o){
			return o instanceof ConcordanceInfo 
					&& ((ConcordanceInfo) o).id == this.id;
		}
		
		@Override
		public int hashCode(){
			return this.id;
		}
		
		@Override
		public String toString(){
			return this.toString();
		}
		
		/**
		 * Get a string that uniquely identifies a Concordance. This is meant to be used in conjunction getFromUniqueDisplayString
		 * @return a string that uniquely identifies a concordance
		 */
		public String getUniqueDisplayString(){
			return this.id + " - " + this.desc;
		}
		
		/**
		 * Get a ConcordanceInfo using a string obtained from getUniqueDisplayString.
		 * @param uniqueDisplayString The string that uniquely identifies a ConcordanceInfo object
		 * @throws IllegalArgumentException if the string passed does not meet the expected format
		 * @return
		 */
		public static ConcordanceInfo getFromUniqueDisplayString(String uniqueDisplayString) {
			try{
				
				Matcher m = UNIQUE_STRING_FORMAT.matcher(uniqueDisplayString);
				
				if(m.matches()){
					int concId = Integer.parseInt(m.group(1));
					String concDesc = m.group(2);
					
					return new ConcordanceInfo(concId, concDesc);
				}else{
					throw new Exception();
				}
			}catch (Exception e) {
				throw new IllegalArgumentException ("Unable to construct object from String. The string is in an invalid format.");
			}
		}
	}

	private int subset = 0;

	private int concordance = 0;

	private DatabaseConnection db = new DatabaseConnection();

	private PreparedStatement containsQuery;

	private PreparedStatement matchingWordQuery;

	private PreparedStatement insertBook;
	private PreparedStatement insertOccurence; 
	private PreparedStatement insertVerse;
	private PreparedStatement insertWord; 
	private PreparedStatement getBooks; 
	private PreparedStatement getWordId;
	private PreparedStatement getVerseId;
	private PreparedStatement getVerseFromWord;
	private PreparedStatement bookExists;
	private PreparedStatement toString;
	private PreparedStatement toStringReferenced;
	private PreparedStatement getCrossChapterWordsByCount;

	private static int versesToDisplay = 25;
	
	private int minVersesToBeNonCrossChapter = 3;

	private String verse_count_table = "select	o1.word, o1.concordance, count(*) xc_occ " +
				" from	occurrence_rec o1, " +
				"		verse_rec v1 " +
				" where	v1.id = o1.verse " +
				//"   and o1.word = 1455 " + //remove
				"   and	v1.concordance = ? " + 
				"   and	v1.concordance = o1.concordance " +
				"   and	not exists (select o2.id " +
				"					from 	occurrence_rec o2, " +
				"							verse_rec v2 " +
				"					where	o2.id < o1.id " +
				"					  and	o2.word = o1.word " +
				"					  and	o2.verse = v2.id " +
				"					  and	v1.book = v2.book " +
				"					  and	v1.chapter = v2.chapter " +
				"					  and	v2.verse_num between (v1.verse_num - 3) and v1.verse_num  " +
				"					)" +
				" group by o1.word, o1.concordance";
	
	public Concordance(String title, String description, File baseDir){
		db.connect(baseDir);
		this.concordance = addConcordance(title, description);
		if(this.concordance != -1){
			initialize();
		}
	}

	public Concordance(int concID, File baseDir){
		db.connect(baseDir);
		this.concordance = concID;
		initialize();

	}

	private void initialize(){
		try{
			getBooks = db.dbConnection.prepareStatement(
					"select * from book_rec where concordance = ?");

			getCrossChapterWordsByCount = db.dbConnection.prepareStatement(//verse_count_table);
					"select w.word, b.id, b.book_name, v.chapter, v.verse_num " +
					" from	word_rec w, " +
					"		book_rec b, " +
					"		verse_rec v, " + 
					"		occurrence_rec o," +
					"		(" + verse_count_table + ") as xc_tbl " +
					" where	w.id = o.word " +
					"   and o.concordance = ? " +
					"	and o.concordance = v.concordance " +
					"   and	o.verse = v.id " +
					"	and b.concordance = v.concordance " +
					"	and	b.id = v.book " +
					"   and	w.id = xc_tbl.word " +
					"   and xc_tbl.concordance = v.concordance " + 
					"   and xc_tbl.xc_occ = ?");
			
			getVerseId = db.dbConnection.prepareStatement(
					"select id " + "" +
					"from verse_rec " +
					"where book = ? " +
					" and chapter = ? " +
					" and verse_num = ?" +
					" and concordance = ?");

			getWordId = db.dbConnection.prepareStatement(
					"select id from word_rec where word = ?");

			getVerseFromWord = db.dbConnection.prepareStatement(
					"select v.*, b.id, b.book_name " +
					"from occurrence_rec occ, word_rec w, verse_rec v, book_rec b " +
					"where w.word = ? " +
					" and occ.word = w.id" +
					" and occ.concordance = ? " +
					" and v.concordance = occ.concordance " +
					" and v.id = occ.verse " +
					" and v.book = b.id");

			insertBook = db.dbConnection.prepareStatement(
					"insert into book_rec (book_name, concordance) values (?, ?)",
					Statement.RETURN_GENERATED_KEYS);

			insertOccurence = db.dbConnection.prepareStatement(
					"insert into occurrence_rec (word, verse, concordance, position) values (?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);

			insertVerse = db.dbConnection.prepareStatement(
					"insert into verse_rec (book, chapter, verse_num, concordance) values (?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);

			insertWord = db.dbConnection.prepareStatement(
					"insert into word_rec (word) values (?)",
					Statement.RETURN_GENERATED_KEYS);

			bookExists = db.dbConnection.prepareStatement(
					"select id from book_rec where book_name = ? and concordance = ?");

			prepareContainsQuery();

			prepareMatchingWordQuery();

			prepareToStringQuerys();

		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("Concordance initialization failed");
			//don't allow anything to proceed if something went wrong in creating the concordance
			System.exit(-1);
		}
	}

	private void prepareToStringQuerys() throws SQLException {
		String query;
		query = "select b.book_name, v.book, v.chapter, v.verse_num, w.word, occ.position"
			+ " from book_rec b, occurrence_rec occ, verse_rec v, word_rec w"
			+ " where v.concordance = " + this.concordance
			+ " and occ.concordance = v.concordance " 
			+ " and occ.verse = v.id"
			+ " and occ.word = w.id"
			+ " and v.book = b.id"
			+ addSubQuery("occ")
			+ " order by word";
		toString = db.dbConnection.prepareStatement(query);

		query = "select b.book_name, v.book, v.chapter, v.verse_num, w.word"
			+ " from book_rec b, occurrence_rec occ, verse_rec v, word_rec w"
			+ " where v.concordance = " + this.concordance
			+ " and occ.concordance = v.concordance " 
			+ " and occ.verse = v.id"
			+ " and occ.word = w.id"
			+ " and v.book = b.id"
			+ addSubQuery("occ")
			+ " order by book, chapter, verse_num, word";
		toStringReferenced = db.dbConnection.prepareStatement(query);
	}

	private void prepareMatchingWordQuery() throws SQLException {
		String query;
		query = "select b.book_name, v.book, v.chapter, v.verse_num, w.word"
			+ " from book_rec b, occurrence_rec occ, verse_rec v, word_rec w"
			+ " where v.concordance = " + this.concordance
			+ " and occ.concordance = v.concordance " 
			+ " and occ.verse = v.id"
			+ " and occ.word = w.id"
			+ " and v.book = b.id"
			+ " and w.word like ?"
			+ addSubQuery("occ")
			+ " order by word";
		matchingWordQuery = db.dbConnection.prepareStatement(query);
	}

	private void prepareContainsQuery() throws SQLException {
		String query = "select count(*) "
			+ " from occurrence_rec occ, word_rec w "
			+ " where occ.concordance = " + this.concordance 
			+ "  and occ.word = w.id"
			+ "  and w.word = ?";
			//+ addSubQuery("occ");
			//+ " group by occ.word";
		containsQuery = db.dbConnection.prepareStatement(query);
	}

	private String addSubQuery(String occRecName){
		if (subset == 0){
			return "";
		}else{
			String query = " and " + occRecName + ".word in (select distinct word "
			+ " from occurrence_rec " 
			+ " where concordance = " + this.concordance
			+ " group by word "
			+ " having count(word) = " + this.subset + ")";
			return query;
		}
	}


	public int addBook(String book){
		int bookIndex = -1;
		try{
			//check if exists
			bookExists.setString(1, book);
			bookExists.setInt(2, this.concordance);
			ResultSet rs = bookExists.executeQuery();
			if(rs.next()){
				bookIndex = rs.getInt("id");
			}else{
				//if this book currently doesn't exist, create it
				insertBook.setString(1, book);
				insertBook.setInt(2, this.concordance);
				insertBook.execute();
				ResultSet index = insertBook.getGeneratedKeys();
				if(index.next()){
					bookIndex = index.getInt(1);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}

		return bookIndex;
	}

	public int addConcordance(String title, String description){
		int concId = -1;
		try {
			PreparedStatement insert = db.dbConnection.prepareStatement(
					"insert into concordance_table (title, description) values(?,?)",
					Statement.RETURN_GENERATED_KEYS);
			insert.setString(1, title);
			insert.setString(2, description);
			insert.execute();
			ResultSet rs = insert.getGeneratedKeys();
			if(rs.next()){
				concId = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return concId;
	}

	public void addWord(String word, Verse ref){
		addWord(word, ref, 0);
	}

	public int addWord(String word, Verse ref, int position){
		//insert word + ref		
		int occurence_id = -1;
		try{
			int verseId = getVerseId(ref);
			if(verseId == -1){
				verseId = insertVerse(ref);
			}

			int wordId = getWordId(word);
			if(wordId == -1){
				wordId = insertWord(word);
			}

			insertOccurence.setInt(1, wordId);
			insertOccurence.setInt(2, verseId);
			insertOccurence.setInt(3, concordance);
			insertOccurence.setInt(4, position); //FIX ME....this is supposed to be the position of the word in the verse
			insertOccurence.execute();

			ResultSet rs = insertOccurence.getGeneratedKeys();
			if(rs.next()){
				occurence_id = rs.getInt(1);
				System.out.println("'" + word + "' added at " + ref);
			}else{
				System.err.println("Something went wrong at addWord(" + word + "," + ref + "," + position +")");
			}


		}catch(SQLException e){
			e.printStackTrace();
		}
		return occurence_id;
	}

	public void addWord(String word, ArrayList<Verse> refs, ArrayList<Integer> positions){
		try{
			for(int i = 0; i < refs.size(); i++){
				Verse ver = refs.get(i);
				int pos = positions.get(i);
				addWord(word, ver, pos);
			}
		}catch(IndexOutOfBoundsException e){
			e.printStackTrace();
			return;
		}
	}

	public int getBookIndex(String book){
		return -1;
	}

	public HashMap<Integer, String> getBooks(){
		HashMap<Integer, String> books = new HashMap<Integer, String>();
		try{
			getBooks.setInt(1, this.concordance);
			ResultSet bookSet = getBooks.executeQuery();
			
			while(bookSet.next()){
				books.put(bookSet.getInt("id"), bookSet.getString("book_name"));
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return books;
	}

	public int getConcId(){
		return this.concordance;
	}

	public String getContext(Verse verse){
		return null;
	}

	public void getMatchingWordSet(String pattern, ConcordanceOutput<String, Verse> output){
		if(pattern == null || pattern.trim().length() == 0){
			return;
		}
	
		try {
			matchingWordQuery.setString(1, pattern + "%");
			ResultSet rs = matchingWordQuery.executeQuery();
	
			Verse curVerse;
			while(rs.next()){
				curVerse = new Verse(rs.getInt("book"), rs.getString("book_name"),rs.getShort("chapter"),rs.getShort("verse_num"));
				output.addValueToKey(rs.getString("word"), curVerse);
			}
			//take care of the final word
			output.flush();
	
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void getMultipleMatchingWordSet(ConcordanceOutput<String, Verse> output, String... patterns){
		if(patterns == null || patterns.length == 0){
			return;
		}

		try{
			String select = "select b.book_name, v.book, v.chapter, v.verse_num";
			String from = " from verse_rec v, book_rec b";
			String where = " where v.concordance = " + this.concordance +
			" and b.id = v.book";
			String orderBy = "order by ";
	
			String curPat;
			for (int i = 0; i < patterns.length; i++){
				curPat = patterns[i];
				select += ", w"+i+".word";
				from += ", occurrence_rec oc"+i+", word_rec w"+i;
				where += " and oc"+i+".concordance = v.concordance "
				+ " and oc"+i+".verse = v.id"
				+ " and oc"+i+".word = w"+i+".id"
				+ " and w"+i+".word like '"+curPat+"%'";
				//+ addSubQuery("oc"+i);
				orderBy += "w"+i + ".word,"; 
			}
			orderBy += " v.book, v.chapter, v.verse_num";
	
			//execute query
			System.out.println(select + from + where);
			ResultSet rs = db.dbConnection.createStatement().executeQuery(select + from + where + orderBy);
			//pull values
			//String prevWord = null;
			//String curWord;
			String curWords = "";
			
			Verse curVerse;
			while(rs.next()){
				System.out.println(rs.getString(1) + " " + rs.getInt(3) + ":" + rs.getInt(4));
				System.out.print("\t");
				
	
				for(int i = 5; i < patterns.length + 5; i++){
					curWords += rs.getString(i) + " ";
					//System.out.println("   added " + rs.getString(i) + " to curWords");
				}
				/*for(int i = 5; i < 5 + patterns.length; i++){
					System.out.print(rs.getString(i) + " ");
				}*/
				System.out.print(curWords);
				System.out.println();
	
				/*if(!prevWords.equals(curWords) && prevWords.size() > 0){
					formatOccurences(prevWords, verseList, result);
				}/*else{
					System.out.println(" - " + curWords);
					System.out.println(" - " + prevWords);
				}*/

				curVerse = new Verse(rs.getInt("book"), rs.getString("book_name"),rs.getShort("chapter"),rs.getShort("verse_num"));
				output.addValueToKey(curWords, curVerse);
			}
	
			//take care of the final word
			output.flush();
		}catch(Exception e){
			e.printStackTrace();
		}
	
		return; 
	}

	public ArrayList<Verse> getOccurrences(String word){
		ArrayList<Verse> refs = new ArrayList<Verse>();

		try{
			getVerseFromWord.setString(1, word);
			getVerseFromWord.setInt(2, concordance);
			ResultSet results = getVerseFromWord.executeQuery();
			if(results.next()){
				do{
					refs.add(new Verse(results.getInt("id"), results.getString("book_name"), results.getShort("chapter"), results.getShort("verse_num")));
				}while(results.next());
			}
		}catch(SQLException e){
			e.printStackTrace();
		}

		//the query failed. return empty list
		return refs;
	}

	public String getOccurrencesStr(String word){
		StringBuilder str = new StringBuilder();
		ArrayList<Verse> occs = getOccurrences(word);
		for(Verse ver : occs){
			str.append(ver.toString() + " ");
		}
		return str.toString();

	}
	
	public String getOccurrenceBlocksByNum(int numOfOccurrences, ConcordanceOutput<String, Verse> output){
		System.out.println("Entering method");
		
		try{
			getCrossChapterWordsByCount.setInt(1, this.concordance);
			getCrossChapterWordsByCount.setInt(2, this.concordance);
			getCrossChapterWordsByCount.setInt(3, numOfOccurrences);
			ResultSet rs = getCrossChapterWordsByCount.executeQuery();
			
			while(rs.next()){
				Verse curVerse = new Verse(rs.getInt(2), rs.getString(3), rs.getShort(4), rs.getShort(5));
				output.addValueToKey(rs.getString(1), curVerse);
			}
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		System.out.println("Exiting method");
		return null;
	}

	private int getVerseId(Verse ref){
		int verseId = -1;
		try {
			getVerseId.setInt(1, ref.getBookIndex());
			getVerseId.setShort(2, ref.getChapter());
			getVerseId.setShort(3, ref.getVerse());
			getVerseId.setInt(4, this.concordance);
			ResultSet results = getVerseId.executeQuery();
			if(results.next()){
				verseId = results.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return verseId;
	
	}

	private int getWordId(String word){
		int wordId = -1;
		try {
			getWordId.setString(1, word);
			ResultSet results = getWordId.executeQuery();
			if(results.next()){
				wordId = results.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return wordId;
	}

	/**
	 * returns the set of words that only occurs the specified number of times
	 * of "set" of 0 returns the full concordance
	 * @param set number of occurences 
	 * @return a string of words that only occur "set" times
	 */
	public void getWordSetWithVerses(int set, ConcordanceOutput<String, Verse> output) {
		setSubset(set);
	
		try{
			ResultSet rs = toString.executeQuery();
	
			String curWord;
			Verse curVerse;
			while(rs.next()){
				curWord = rs.getString("word");
				curVerse = new Verse(rs.getInt("book"), rs.getString("book_name"), rs.getShort("chapter"), rs.getShort("verse_num"));
				output.addValueToKey(curWord, curVerse);
			}
			output.flush();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
	
	}
	
	public ArrayList<String> getWordSet(int set) {
		setSubset(set);
		ArrayList<String> words = new ArrayList<String>();
		try{
			ResultSet rs = toString.executeQuery();
			String curWord = null;
			
			while (rs.next()){
				curWord = rs.getString("word");
				if(!words.contains(curWord)){
					words.add(curWord);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return words;
	}

	public void getWordSetReferenced(int set, ConcordanceOutput<Verse, String> output) {
		setSubset(set);

		try{
			ResultSet rs = toStringReferenced.executeQuery();
	
			Verse curVerse = null;
			while(rs.next()){
				curVerse = new Verse(rs.getInt("book"), rs.getString("book_name"),rs.getShort("chapter"),rs.getShort("verse_num"));

				output.addValueToKey(curVerse, rs.getString("word"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
	}

	public boolean hasWord(String word){
		//execute query
		ResultSet rs;
		try {
			containsQuery.setString(1, word);
			rs = containsQuery.executeQuery();
	
			//check if there were any rows returned
			if (rs.next()){ 
				int count = rs.getInt(1);
				if((subset == 0 && count > 0) || (subset != 0 && count == subset)){
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return false;
	}

	private int insertVerse(Verse ref) throws SQLException {
		insertVerse.setInt(1, ref.getBookIndex());
		insertVerse.setInt(2, ref.getChapter());
		insertVerse.setInt(3, ref.getVerse());
		insertVerse.setInt(4, concordance);
		insertVerse.execute();
		ResultSet index = insertVerse.getGeneratedKeys();
		if(index.next()){
			return index.getInt(1);
		}else{
			return -1;
		}
	}

	private int insertWord(String word){
		int wordId = -1;
		try{
			insertWord.setString(1, word);
			insertWord.execute();
			ResultSet index = insertWord.getGeneratedKeys();
			if(index.next()){
				wordId = index.getInt(1);
			}else {
				System.out.println("FAIL retrieving word ID");
			}

		}catch(SQLException e){
			e.printStackTrace();
		}

		return wordId;
	}

	public static int getDisplayLength(){
		return versesToDisplay;
	}
	
	public static void setDisplayLength(int length){
		versesToDisplay = length;
	}

	public void setSubset(int subset){
		this.subset = subset;
		try{
			prepareToStringQuerys();

			prepareContainsQuery();

			prepareMatchingWordQuery();
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public int size(){
		return 0;
	}


	public String toString(){
		StringWriter writer = new StringWriter();
		ConcordanceOutputStream<String, Verse> output = new ConcordanceOutputStream<String, Verse>(writer, ConcordanceOutputStream.ListStyle.ONE_PER_LINE_TRUNCATED);
		getWordSetWithVerses(0, output);
		return writer.toString();
	}

	/**
	 * works effectivly the same as toString() except that html tags are embedded to like each reference to the Biblegateway.com page for it.
	 */
	public String toHTMLString(){
		return "";
	}

}
